<?php
require_once("libs/dbmodel.php");

class Ubicacion extends DbModel {
	
	/**
	 * Use a recursive query (PostgreSQL) to obtains location father and return all path
	 * @param string $text
	 * @return boolean
	 */
	public function getLocationList($text) {
		$sql= "WITH RECURSIVE path(id, ubicacion, nivel, path, padre, id_padre) AS (
			   SELECT 
			      id, ubicacion, nivel, ''||ubicacion, NULL, id_padre 
			      FROM ubicacion 
			      WHERE nivel = 0
			   UNION
			   SELECT 
			      u.id, u.ubicacion, u.nivel,
			      u.ubicacion || ', ' || p.path,
			      p.path, u.id_padre
			   FROM ubicacion as u, path as p
			   WHERE p.id = u.id_padre
			)
			SELECT id, path FROM path
			WHERE ubicacion ILIKE '$text%'";
		
		if ($this->execQuery($sql)) {
			return true;
		} else {
			return false;
		}
	}
	
	
	/**
	 * Use a recursive query (PostgreSQL) to obtains location father and return all path
	 * @param string $country
	 * @return boolean
	 */
	public function getLocationListByCountry($country) {
		$sql= "WITH RECURSIVE path(id, ubicacion, nivel, path, padre, id_padre) AS (
				   SELECT
				      id, ubicacion, nivel, ''||ubicacion, NULL, id_padre
				      FROM ubicacion
				      WHERE nivel = 0
					  AND id = $country
				   UNION
				   SELECT
				      u.id, u.ubicacion, u.nivel,
				      CASE (u.nivel)
						WHEN 1 THEN u.ubicacion
						ELSE u.ubicacion || ', ' || p.path
					  END,
				      p.path, u.id_padre
				   FROM ubicacion as u, path as p
				   WHERE p.id = u.id_padre
				)
				SELECT id, path, nivel, id_padre FROM path
				WHERE nivel <> 0";
	
		if ($this->execQuery($sql)) {
			return true;
		} else {
			return false;
		}
	}
}

?>
